package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.CashReturnDataList;
import com.mbc.rmt.entities.CashReturn;
import com.mbc.rmt.entities.CashReturnRpt;

public class CashReturnDAOImpls extends DAO<CashReturn> implements CashReturnDAO {

	public CashReturnDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CashReturn";
	}
	
	public static final String field_slip_id = "SlipID";
	public static final String field_slip_no = "SlipNo";
	public static final String field_created_date = "CreatedDate";
	public static final String field_modified_date = "ModifiedDate";
	public static final String field_tr_date = "TRDate";
	public static final String field_typeid = "TypeID";
	public static final String field_typecode = "type_code";
	public static final String field_created_by = "CreatedBy";
	public static final String field_modified_by = "ModifiedBy";
	public static final String field_agent_id = "AgentID";
	public static final String field_agent_code = "agent_code";
	public static final String field_defaultbranch_id = "DefaultBranchID";
	public static final String field_defaultbranch_code = "BranchCode";
	public static final String field_payment_amt = "PaymentAmount";
	public static final String field_bank_id = "BankID";
	public static final String field_banck_code = "BankCode";
	public static final String field_tr_type = "TRType";
	public static final String field_remark = "Remark";
	public static final String field_currency_id = "CurrencyID";
	public static final String field_amount = "Amount";
	
	enum TRAN_MODE { SAVE, UPDATE }
	
	@Override
	public void create(CashReturn entity) throws SQLException {

      query = insertParam(table_name, new String[] { field_slip_id,field_slip_no,
    		  field_tr_date, field_agent_id,
    		  field_defaultbranch_id,field_payment_amt, field_typeid,
    		  field_bank_id,field_tr_type, field_created_date, field_created_by, field_remark});
      pstmt = conn.prepareStatement(query);
	  updateRecord(entity, TRAN_MODE.SAVE);
	  pstmt.executeUpdate();
	  pstmt.close();
	}

	@Override
	public void update(CashReturn entity) throws SQLException {
		
		query = updateParam(table_name, new String[] { field_slip_id,field_slip_no, 
	    		  field_tr_date, field_agent_id,
	    		  field_defaultbranch_id,field_payment_amt, field_typeid,
	    		  field_bank_id,field_tr_type, field_modified_date, field_modified_by, field_remark }) + " AND " 
	    		  + field_slip_id + " = " + entity.getSlipid();
	    pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.UPDATE);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " WHERE " + field_slip_id + " = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashReturn findByCode(String slipNo)
			throws SQLException {
		CashReturn l_entity = null;
		query = getSelectQuery() + " WHERE " + field_slip_no + "'" + slipNo + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_entity = readRecord(rs);
		}
		return l_entity;
	}

	@Override
	public CashReturn findById(long slipId) throws SQLException {
		CashReturn l_entity = null;
		query = getSelectQuery() + " WHERE " + field_slip_id + " = " +  slipId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_entity = readRecord(rs);
		}
		return l_entity;
	}

	@Override
	public CashReturnDataList findCashReturnByCri(CashReturn cri)
			throws SQLException {
		CashReturnDataList l_dataList = new CashReturnDataList();
		ArrayList<CashReturn> l_entityList = new ArrayList<CashReturn>();
		
		/*String l_cri = " and SlipNo like N'%" + cri.getKeyword() + "%'"
		                + " or TRDate like N'%" + cri.getKeyword() + "%'";*/
		String l_cri = "";
		
		if(!cri.getFieldname().equals("TRDate"))
			l_cri += " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%')";
		else
			l_cri += " and ((Convert(varchar(100)," + cri.getFieldname() + ", 103)) like N'%" + cri.getKeyword() + "%')";
		
		if(!cri.isTyping()) {
			query = " set rowcount "
					+ cri.getSize()
					+ " select * from (select row_number() over(order by SlipID desc)No, cr.*, "
					+ "t.type_code, a.agent_code, b.BranchCode, bk.BankCode "
					+ " from CashReturn cr "
					+ " left join [Type] t on t.[type_id] = cr.TypeID"
					+ " left join Agent a on a.agent_id = cr.AgentID"
					+ " inner join Branch b on b.BranchId = cr.DefaultBranchID"
					+ " left join Bank bk on bk.BankId = cr.BankID"
					+ " where 1=1 " + l_cri + " ) a ";
			if(cri.getStatus().equals("10"))
				query += " where a.DefaultBranchID = " + cri.getBranchId() + " and No > " + cri.getIndex();
			else
				query += " where a.DefaultBranchID = " + cri.getBranchId() + " and a.TRType = " + cri.getStatus() + " and No > " + cri.getIndex();
		}
		else {
			query = "SELECT cr.*, t.type_code, a.agent_code, b.BranchCode, bk.BankCode FROM CashReturn cr " +
					"LEFT JOIN [Type] t on t.[type_id] = cr.TypeID " +
					"LEFT JOIN Agent a on a.agent_id = cr.AgentID " +
					"INNER JOIN Branch b on b.BranchId = cr.DefaultBranchID " +
					"LEFT JOIN Bank bk on bk.BankId = cr.BankID WHERE 1 = 1 AND cr.DefaultBranchID = " + cri.getBranchId() + " AND";
		
			if(cri.getStatus().equals("10"))
				query += " cr.SlipNo = N'" + cri.getSlipno() + "'";
			else
				query += " cr.TRType = " + cri.getStatus() + " AND cr.SlipNo = N'" + cri.getSlipno() + "'";
		}
		
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		while(rs.next()){
			CashReturn l_entity = readRecord(rs);
			
			l_entity.setTypecode(rs.getString(field_typecode) == null ? "" : rs.getString(field_typecode));
			l_entity.setAgentcode(rs.getString(field_agent_code) == null ? "" : rs.getString(field_agent_code));
			l_entity.setDefaultbranchcode(rs.getString(field_defaultbranch_code) == null ? "" : rs.getString(field_defaultbranch_code));
			l_entity.setBankCode(rs.getString(field_banck_code) == null ? "" : rs.getString(field_banck_code));
			
			l_entityList.add(l_entity);
		}
		
		l_dataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		
		if(!cri.isTyping()) {
			query = "select count(*) from (select * from " +table_name + " cr where 1=1"+l_cri +" ) a where 1 = 1";
			
			if(cri.getStatus().equals("10"))
				query += " AND a.DefaultBranchID = " + cri.getBranchId();
			else
				query += " and a.TRType = " + cri.getStatus() + " AND a.DefaultBranchID = " + cri.getBranchId();
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				l_dataList.setTotalRecord(rs.getLong(1));
			}
			
			rs.close();
			pstmt.close();
		}
		return l_dataList;
	}

	protected void updateRecord(CashReturn entity, TRAN_MODE mode) throws SQLException {
		int i = 1;
		pstmt.setLong(i++, entity.getSlipid());
		pstmt.setString(i++, entity.getSlipno());
		pstmt.setTimestamp(i++, new Timestamp(entity.getTrdate().getTime()));
		
		if(entity.getAgentid() != null)
			pstmt.setLong(i++, entity.getAgentid());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getDefaultbranchid());
		pstmt.setDouble(i++, entity.getPaymentamount());
		if(entity.getTypeid() != null)
		    pstmt.setLong(i++, entity.getTypeid());
		else
			pstmt.setString(i++, null);
		if(entity.getBankid() != null)
			pstmt.setLong(i++, entity.getBankid());
		else
			pstmt.setString(i++, null);
		pstmt.setByte(i++, entity.getTrtype());
		if(mode == TRAN_MODE.UPDATE) {
			pstmt.setTimestamp(i++, new Timestamp(entity.getModifieddate().getTime()));
			pstmt.setLong(i++, entity.getModifiedby());
		} else {
			pstmt.setTimestamp(i++, new Timestamp(entity.getCreatedate().getTime()));
			pstmt.setLong(i++, entity.getCreatedby());
		}
		pstmt.setString(i++, entity.getRemark());
	}
	@Override
	protected CashReturn readRecord(ResultSet rs) throws SQLException {
		CashReturn l_entity = new CashReturn();
		l_entity.setSlipid(rs.getLong(field_slip_id));
		l_entity.setSlipno(rs.getString(field_slip_no));
		l_entity.setCreatedate(rs.getTimestamp(field_created_date));
		l_entity.setModifieddate(rs.getTimestamp(field_modified_date));
		l_entity.setTrdate(rs.getTimestamp(field_tr_date));
		l_entity.setCreatedby(rs.getLong(field_created_by));
		l_entity.setModifiedby(rs.getLong(field_modified_by));
		l_entity.setAgentid(rs.getLong(field_agent_id));
		l_entity.setDefaultbranchid(rs.getLong(field_defaultbranch_id));
		l_entity.setPaymentamount(rs.getDouble(field_payment_amt));
		l_entity.setBankid(rs.getLong(field_bank_id));
		l_entity.setTrtype(rs.getByte(field_tr_type));
		l_entity.setTypeid(rs.getLong(field_typeid));
//		l_entity.setTypecode(rs.getString(field_typecode));
//		l_entity.setAgentcode(rs.getString(field_agent_code));
//		l_entity.setDefaultbranchcode(rs.getString(field_defaultbranch_code));
//		l_entity.setBankCode(rs.getString(field_banck_code));
		l_entity.setRemark(rs.getString(field_remark));
		
		return l_entity;
	}

	@Override
	public ResultSet prepareCashReturnReport(CashReturnRpt entity)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "[dbo].[sp_cr_CashReturnRpt] ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";
		
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashReturnRptParameter(l_pstmt, entity);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private void CashReturnRptParameter(CallableStatement l_pstmt,
			CashReturnRpt entity) throws SQLException{
		
		int i = 1;
		l_pstmt.setString(i++, new java.sql.Timestamp(entity.getFromDate().getTime()).toString());
		l_pstmt.setString(i++, new java.sql.Timestamp(entity.getTodate().getTime()).toString());
		l_pstmt.setString(i++, entity.getFromSlipNo());
		l_pstmt.setString(i++, entity.getToSlipNo());
		l_pstmt.setString(i++, entity.getFromBranchID());
		l_pstmt.setString(i++, entity.getToBranchID());
		l_pstmt.setString(i++, entity.getFromAgentID());
		l_pstmt.setString(i++, entity.getToAgentID());
		l_pstmt.setString(i++, entity.getFromTypeID());
		l_pstmt.setString(i++, entity.getToTypeID());
		l_pstmt.setString(i++, entity.getFromUserID());
		l_pstmt.setString(i++, entity.getToUserID());
		l_pstmt.setString(i++, entity.getFromCurrencyID());
		l_pstmt.setString(i++, entity.getToCurrencyID());
		l_pstmt.setString(i++, entity.getFromReceiptNo());
		l_pstmt.setString(i++, entity.getToReceiptNo());
		l_pstmt.setString(i++, entity.getFromBank());
		l_pstmt.setString(i++, entity.getToBank());
		l_pstmt.setString(i++, entity.getOrderby());
		l_pstmt.setLong(i++, entity.getBranchID());
		
	}

	@Override
	public CashReturn getCashReturn(CashReturn entity) throws SQLException {
		CashReturn cashreturn = null;
		query = " select cr.*,ISNULL(a.agent_code,'')agent_code, crd.CurrencyID, crd.Amount " +
				" from CashReturn cr " +
				" left join CashReturnDetails crd on crd.SlipID=cr.SlipID " +
				" left join Agent a on a.agent_id=cr.AgentID " +
				" where cr.SlipNo = '" + entity.getSlipno() + "'"+
				" and cr.DefaultBranchID = " + entity.getDefaultbranchid() +
				" and cr.TRType = " + entity.getTrtype();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			cashreturn = readRecords(rs);
		rs.close();
		pstmt.close();
		return cashreturn;
	}
	
	protected CashReturn readRecords(ResultSet rs) throws SQLException {
		CashReturn l_entity = new CashReturn();
		l_entity.setSlipid(rs.getLong(field_slip_id));
		l_entity.setSlipno(rs.getString(field_slip_no));
		l_entity.setCreatedate(rs.getTimestamp(field_created_date));
		l_entity.setModifieddate(rs.getTimestamp(field_modified_date));
		l_entity.setTrdate(rs.getTimestamp(field_tr_date));
		l_entity.setCreatedby(rs.getLong(field_created_by));
		l_entity.setModifiedby(rs.getLong(field_modified_by));
		l_entity.setAgentid(rs.getLong(field_agent_id));
		l_entity.setDefaultbranchid(rs.getLong(field_defaultbranch_id));
		l_entity.setPaymentamount(rs.getDouble(field_payment_amt));
		l_entity.setBankid(rs.getLong(field_bank_id));
		l_entity.setTrtype(rs.getByte(field_tr_type));
		l_entity.setTypeid(rs.getLong(field_typeid));
		l_entity.setRemark(rs.getString(field_remark));
		l_entity.setAgentcode(rs.getString(field_agent_code));
		l_entity.setCurrencyid(rs.getLong(field_currency_id));
		l_entity.setAmount(rs.getDouble(field_amount));
		
		return l_entity;
	}
	
}
